﻿CREATE TABLE [dbo].[User](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[Firstname] [varchar](30) NOT NULL,
	[Lastname] [varchar](30) NOT NULL,
	[Username] [varchar](30) NOT NULL,
	[Password] [varchar](30) NOT NULL,
	[Sex] [varchar](1) NOT NULL,
                [Description] [varchar](2000),
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Room](
	[RoomID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED 
(
	[RoomID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[LoggedInUser](
	[LoggedInUserID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[RoomID] [int] NOT NULL,
 CONSTRAINT [PK_LoggedInUser] PRIMARY KEY CLUSTERED 
(
	[LoggedInUserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Message](
	[MessageID] [int] IDENTITY(1,1) NOT NULL,
	[RoomID] [int] NULL,
	[UserID] [int] NOT NULL,
	[ToUserID] [int] NULL,
	[Text] [varchar](100) NOT NULL,
	[TimeStamp] [datetime] NOT NULL CONSTRAINT [DF_Message_TimeStamp]  DEFAULT (getdate()),
	[Color] [varchar](50) NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED 
(
	[MessageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[LoggedInUser]  WITH CHECK ADD  CONSTRAINT [FK_LoggedInUser_Room] FOREIGN KEY([RoomID])
REFERENCES [dbo].[Room] ([RoomID])

ALTER TABLE [dbo].[LoggedInUser] CHECK CONSTRAINT [FK_LoggedInUser_Room]

ALTER TABLE [dbo].[LoggedInUser]  WITH CHECK ADD  CONSTRAINT [FK_LoggedInUser_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])

ALTER TABLE [dbo].[LoggedInUser] CHECK CONSTRAINT [FK_LoggedInUser_User]

ALTER TABLE [dbo].[Message]  WITH CHECK ADD  CONSTRAINT [FK_Message_Room] FOREIGN KEY([RoomID])
REFERENCES [dbo].[Room] ([RoomID])

ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [FK_Message_Room]

ALTER TABLE [dbo].[Message]  WITH CHECK ADD  CONSTRAINT [FK_Message_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])

ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [FK_Message_User]

ALTER TABLE [dbo].[Message]  WITH CHECK ADD  CONSTRAINT [FK_Message_User1] FOREIGN KEY([ToUserID])
REFERENCES [dbo].[User] ([UserID])

ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [FK_Message_User1]